package dao.Impl;

import context.BaseContext;
import dao.BaseDao;
import dao.SetmealDao;
import model.DTO.SetmealAddDTO;
import model.DTO.SetmealEditDTO;
import model.DTO.SetmealPageDTO;
import model.VO.SetmealByIdVO;
import model.VO.SetmealVO;
import model.entity.Setmeal;
import model.entity.SetmealDish;
import util.NowTime;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class SetmealDaoImpl extends BaseDao implements SetmealDao {
    //修改套餐
    @Override
    public void edit(SetmealEditDTO setmealEditDTO) {
//        String sqlUpd="update setmeal set image='',price=1,name='',update_time='',update_user='',description=''  where id=1;";
        String sqlUpd="update setmeal set image=?,price=?,name=?,update_time=?,update_user=?,category_id=?";
        int flagD=0;
        int flagS=0;
        try {
            NowTime nowTime = new NowTime();
            String t = nowTime.getTime();
            int userId = BaseContext.getCurrentId();
            if(setmealEditDTO.getStatus()!=null){
                sqlUpd+=",status=?";
                flagS=1;
            }
            if(setmealEditDTO.getDescription()!=null&&setmealEditDTO.getDescription().isEmpty()){
                sqlUpd+=",description=?";
                flagD=1;
            }

            sqlUpd+=" where id=?;";
            pstmt=conn.prepareStatement(sqlUpd);
            pstmt.setString(1, setmealEditDTO.getImage());
            pstmt.setDouble(2,setmealEditDTO.getPrice());
            pstmt.setString(3,setmealEditDTO.getName());
            pstmt.setTimestamp(4, Timestamp.valueOf(t));
            pstmt.setInt(5,userId);
            pstmt.setInt(6,setmealEditDTO.getCategoryId());

            if(flagD==1&&flagS==1){
                pstmt.setInt(7,setmealEditDTO.getStatus());
                pstmt.setString(8, setmealEditDTO.getDescription());
                pstmt.setInt(9,setmealEditDTO.getId());
            } else if (flagD==1&&flagS==0) {
                pstmt.setString(7, setmealEditDTO.getDescription());
                pstmt.setInt(8,setmealEditDTO.getId());
            } else if (flagD==0&&flagS==1) {
                pstmt.setInt(7,setmealEditDTO.getStatus());
                pstmt.setInt(8,setmealEditDTO.getId());
            } else{
                pstmt.setInt(7,setmealEditDTO.getId());
            }
            pstmt.executeUpdate();

            String sqlDel="delete from setmeal_dish where setmeal_id=?;";
            pstmt=conn.prepareStatement(sqlDel);
            pstmt.setInt(1,setmealEditDTO.getId());
            pstmt.executeUpdate();

            List<SetmealDish> setmealDishList = setmealEditDTO.getSetmealDishes();
            for(SetmealDish sd:setmealDishList){
                String sqlIns="insert into setmeal_dish (setmeal_id, dish_id, name, price, copies) VALUES (?,?,?,?,?);";
                pstmt=conn.prepareStatement(sqlIns);
                pstmt.setInt(1,sd.getSetmealId());
                pstmt.setInt(2,sd.getDishId());
                pstmt.setString(3,sd.getName());
                pstmt.setDouble(4,sd.getPrice());
                pstmt.setInt(5,sd.getCopies());
                pstmt.executeUpdate();
            }
            System.out.println("修改套餐成功");
        } catch (SQLException e) {
            System.out.println("修改套餐失败"+e);
        }
    }

    //套餐分页查询
    @Override
    public List<SetmealVO> page(SetmealPageDTO setmealPageDTO) {
        List<SetmealVO> records=new ArrayList<>();
        String sql="select s.id,s.name,s.category_id,s.price,s.image,s.description,s.status,s.update_time,c.name from setmeal s left join category c on s.category_id=c.id where 1=1";
        if(setmealPageDTO.getName()!=null && !setmealPageDTO.getName().isEmpty()){
            sql+=" and s.name like '%"+setmealPageDTO.getName()+"%'";
        }
        if(setmealPageDTO.getCategoryId()!=null){
            sql+=" and c.id="+(int)setmealPageDTO.getCategoryId();
        }
        if(setmealPageDTO.getStatus()!=null){
            sql+=" and s.status="+(int)setmealPageDTO.getStatus();
        }
        sql+=" limit ?,? ;";
        int page=setmealPageDTO.getPage();
        int pageSize= setmealPageDTO.getPageSize();
        page=(page-1)*pageSize;
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,page);
            pstmt.setInt(2,pageSize);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while(rs.next()){
                SetmealVO s=new SetmealVO();
                s.setId(rs.getInt("s.id"));
                s.setName(rs.getString("s.name"));
                s.setCategoryId(rs.getInt("s.category_id"));
                s.setPrice(rs.getDouble("s.price"));
                s.setImage(rs.getString("s.image"));
                s.setDescription(rs.getString("s.description"));
                s.setStatus(rs.getInt("s.status"));
                s.setUpdateTime(rs.getString("s.update_time"));
                s.setCategoryName(rs.getString("c.name"));
                System.out.println(s);
                records.add(s);
            }
            System.out.println("套餐分页查询成功");
        } catch (SQLException e) {
            System.out.println("套餐分页查询失败："+e);
        }
        return records;
    }

    @Override
    public int count(SetmealPageDTO setmealPageDTO) {
        int total=0;
        String sql="select s.id,s.name,s.category_id,s.price,s.image,s.description,s.status,s.update_time,c.name from setmeal s left join category c on s.category_id=c.id where 1=1";
        if(setmealPageDTO.getName()!=null && !setmealPageDTO.getName().isEmpty()){
            sql+=" and s.name like '%"+setmealPageDTO.getName()+"%'";
        }
        if(setmealPageDTO.getCategoryId()!=null){
            sql+=" and c.id="+(int)setmealPageDTO.getCategoryId();
        }
        if(setmealPageDTO.getStatus()!=null){
            sql+=" and s.status="+(int)setmealPageDTO.getStatus();
        }
        sql+=" limit ?,? ;";
        int page=setmealPageDTO.getPage();
        int pageSize= setmealPageDTO.getPageSize();
        page=(page-1)*pageSize;
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,page);
            pstmt.setInt(2,pageSize);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while(rs.next()){
               total+=1;
            }
            System.out.println("套餐分页查询计数成功");
        } catch (SQLException e) {
            System.out.println("套餐分页查询计数失败："+e);
        }
        return total;
    }

    //修改套餐售卖状态
    @Override
    public void statusEdit(int id) {
        String sqlSelect="select status from setmeal where id=?;";
        String sqlUpdate="update setmeal set status=?,update_time=?,update_user=? where id=?;";
        try {
            NowTime nowTime = new NowTime();
            String t = nowTime.getTime();
            int userId = BaseContext.getCurrentId();
            int status = -1;

            pstmt=conn.prepareStatement(sqlSelect);
            pstmt.setInt(1,id);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while(rs.next()){
                status=rs.getInt("status");
            }
            if(1==status){
                status=0;
                pstmt=conn.prepareStatement(sqlUpdate);
                pstmt.setInt(1,status);
                pstmt.setTimestamp(2, Timestamp.valueOf(t));
                pstmt.setInt(3,userId);
                pstmt.setInt(4,id);
                pstmt.executeUpdate();
            }
            else {
                status=1;
                pstmt=conn.prepareStatement(sqlUpdate);
                pstmt.setInt(1,status);
                pstmt.setTimestamp(2, Timestamp.valueOf(t));
                pstmt.setInt(3,userId);
                pstmt.setInt(4,id);
                pstmt.executeUpdate();
            }
            System.out.println("修改套餐售卖状态成功");
        } catch (SQLException e) {
            System.out.println("修改套餐售卖状态失败"+e);
        }
    }

    //批量删除套餐
    @Override
    public void del(String ids) {
        String[] delIds=ids.split(",");
        for (String delId : delIds) {
            String sql="delete from setmeal where id=?;";
            String sqlSd="delete from setmeal_dish where setmeal_id=?;";
            try {
                pstmt=conn.prepareStatement(sql);
                pstmt.setInt(1, Integer.parseInt(delId));
                pstmt.executeUpdate();

                pstmt=conn.prepareStatement(sqlSd);
                pstmt.setInt(1, Integer.parseInt(delId));
                pstmt.executeUpdate();
                System.out.println("批量删除套餐成功");
            } catch (SQLException e) {
                System.out.println("批量删除套餐失败"+e);
            }
        }
    }

    //新增套餐
    @Override
    public void add(SetmealAddDTO setmealAddDTO) {
        NowTime nowTime = new NowTime();
        String t = nowTime.getTime();
        int userId = BaseContext.getCurrentId();
        List<SetmealDish> setmealDishes=setmealAddDTO.getSetmealDishes();
        String sqlS="insert into setmeal (category_id, name, price,status, description, image, create_time, create_user) values (?,?,?,?,?,?,?,?);";
        String sqlSel="select id from setmeal where name=?;";
        String sqlSD="insert into setmeal_dish (setmeal_id, dish_id, name, price, copies) VALUES (?,?,?,?,?);";
        try {
            conn.setAutoCommit(false);
            pstmt=conn.prepareStatement(sqlS);
            pstmt.setInt(1,setmealAddDTO.getCategoryId());
            pstmt.setString(2,setmealAddDTO.getName());
            pstmt.setDouble(3,setmealAddDTO.getPrice());
            pstmt.setInt(4,setmealAddDTO.getStatus());
            pstmt.setString(5,setmealAddDTO.getDescription());
            pstmt.setString(6,setmealAddDTO.getImage());
            pstmt.setTimestamp(7, Timestamp.valueOf(t));
            pstmt.setInt(8,userId);
            pstmt.executeUpdate();

            for(SetmealDish sd : setmealDishes){
                int setmeal_id=-1;
                pstmt=conn.prepareStatement(sqlSel);
                pstmt.setString(1, setmealAddDTO.getName());
                pstmt.executeQuery();
                rs=pstmt.getResultSet();
                while(rs.next()){
                    setmeal_id=rs.getInt("id");
                }

                pstmt=conn.prepareStatement(sqlSD);
                pstmt.setInt(1,setmeal_id);
                pstmt.setInt(2,sd.getDishId());
                pstmt.setString(3,sd.getName());
                pstmt.setDouble(4,sd.getPrice());
                pstmt.setInt(5,sd.getCopies());
                pstmt.executeUpdate();
            }
            conn.commit();
            System.out.println("新增套餐成功");
        } catch (SQLException e) {
            try {
                conn.rollback();
            }catch (SQLException ex){
                System.out.println("事务回滚异常");
            }
            System.out.println("新增套餐失败"+e);
        }
    }

    //根据id查询套餐全部信息
    @Override
    public SetmealByIdVO get(int id) {
        SetmealByIdVO sB=new SetmealByIdVO();
        List<SetmealDish> setmealDishes=new ArrayList<>();
        String sqlS="select s.category_id,c.name,s.description,s.id,s.image,s.name,s.price,s.status,s.update_time from setmeal s left join category c on s.category_id = c.id where s.id=?;";
        String sqlSD="select * from setmeal_dish where setmeal_id=?;";
        try{
            pstmt=conn.prepareStatement(sqlS);
            pstmt.setInt(1,id);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while(rs.next()) {
                sB.setCategoryId(rs.getInt("s.category_id"));
                sB.setCategoryName(rs.getString("c.name"));
                sB.setDescription(rs.getString("s.description"));
                sB.setId(rs.getInt("s.id"));
                sB.setImage(rs.getString("s.image"));
                sB.setName(rs.getString("s.name"));
                sB.setPrice(rs.getDouble("s.price"));
                sB.setStatus(rs.getInt("s.status"));
                sB.setUpdateTime(rs.getString("s.update_time"));
            }

            pstmt=conn.prepareStatement(sqlSD);
            pstmt.setInt(1,id);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while(rs.next()){
                SetmealDish sd=new SetmealDish();
                sd.setCopies(rs.getInt("copies"));
                sd.setSetmealId(rs.getInt("setmeal_id"));
                sd.setId(rs.getInt("id"));
                sd.setDishId(rs.getInt("dish_id"));
                sd.setName(rs.getString("name"));
                sd.setPrice(rs.getDouble("price"));
                setmealDishes.add(sd);
                System.out.println(sd);
            }
            sB.setSetmealDishes(setmealDishes);
            System.out.println("根据id查询套餐成功");

        } catch (SQLException e) {
            System.out.println("根据id查询套餐失败"+e);
        }

        return sB;
    }

    @Override
    public Setmeal getById(Integer setmealId) {
        Setmeal setmeal = new Setmeal();
        String sql="select * from `setmeal` where id=?;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,setmealId);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                setmeal.setId(rs.getInt("id"));
                setmeal.setName(rs.getString("name"));
                setmeal.setDescription(rs.getString("description"));
                setmeal.setPrice(rs.getFloat("price"));
                setmeal.setImage(rs.getString("image"));
                setmeal.setStatus(rs.getInt("status"));
                setmeal.setCategory_id(rs.getInt("category_id"));
                setmeal.setCreate_time(String.valueOf(rs.getTime("create_time")));
                setmeal.setUpdate_time(String.valueOf(rs.getTime("update_time")));
                setmeal.setCreate_user(rs.getInt("create_user"));
                setmeal.setUpdate_user(rs.getInt("update_user"));
            }

        } catch (Exception e) {
            System.out.println("查询套餐失败："+e);
        }
        return setmeal;
    }

    @Override
    public List<Setmeal> list(String name) {
        List<Setmeal> setmealList=new ArrayList<>();
        String sql="select * from `setmeal` where name like '%"+name+"%'";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs=pstmt.getResultSet();
            while (rs.next()){
                Setmeal setmeal = new Setmeal();
                setmeal.setId(rs.getInt("id"));
                setmeal.setName(rs.getString("name"));
                setmeal.setDescription(rs.getString("description"));
                setmeal.setPrice(rs.getFloat("price"));
                setmeal.setImage(rs.getString("image"));
                setmeal.setStatus(rs.getInt("status"));
                setmeal.setCategory_id(rs.getInt("category_id"));
                setmeal.setCreate_time(String.valueOf(rs.getTime("create_time")));
                setmeal.setUpdate_time(String.valueOf(rs.getTime("update_time")));
                setmeal.setCreate_user(rs.getInt("create_user"));
                setmeal.setUpdate_user(rs.getInt("update_user"));
                setmealList.add(setmeal);
            }
        } catch (SQLException e) {
            System.out.println("模糊查询套餐错误:"+e);
        }
        return setmealList;
    }
}
